package com.liushuo.dao;

import com.liushuo.entity.ExerciseSet;
import com.liushuo.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExerciseSetDao {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet generatedKeys = null;

    public void save(ExerciseSet exerciseSet, Connection conn) throws SQLException {
        String sql = "INSERT INTO exercise_sets(create_userid, start_question_id, end_question_id, grade_level, score, create_time) " +
                "VALUES ( ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, exerciseSet.getCreateUserId());
            ps.setLong(2, exerciseSet.getStartQuestionId());
            ps.setLong(3, exerciseSet.getEndQuestionId());
            ps.setString(4, exerciseSet.getGradeLevel());
            ps.setString(5, exerciseSet.getScore());
            ps.setTimestamp(6, exerciseSet.getCreateTime());

            int affectedRows = ps.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        exerciseSet.setId(generatedKeys.getLong(1));
                    }
                }
            }
        }
    }

    public void setExerciseSetScore(Long exerciseSetId, int score) {
        String sql = "UPDATE exercise_sets SET score = ? WHERE id = ?";
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, score);
            ps.setLong(2, exerciseSetId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<ExerciseSet> getExerciseSetByUserIdAndDate(String userId, Date startDate, Date endDate) {
        String sql = "SELECT * FROM exercise_sets WHERE create_userid = ? AND create_time BETWEEN ? AND ?";
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, userId);
            ps.setTimestamp(2, DBUtil.toStartOfDay((java.sql.Date)startDate));
            ps.setTimestamp(3, DBUtil.toEndOfDay((java.sql.Date)endDate));
            ResultSet rs = ps.executeQuery();
            List<ExerciseSet> exerciseSets = new ArrayList<>();
            while (rs.next()) {
                ExerciseSet exerciseSet = ExerciseSet.builder()
                        .id(rs.getLong("id"))
                        .createUserId(rs.getString("create_userid"))
                        .startQuestionId(rs.getLong("start_question_id"))
                        .endQuestionId(rs.getLong("end_question_id"))
                        .gradeLevel(rs.getString("grade_level"))
                        .score(rs.getString("score"))
                        .createTime(rs.getTimestamp("create_time"))
                        .build();
                exerciseSets.add(exerciseSet);
            }
            return exerciseSets;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
